﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for Vacancy
/// </summary>
public class Vacancy
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
	public Vacancy()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    private DataTable SelectVacancyID()
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("SELECT * FROM SelectVacancyID", conn);
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception)
        {
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return tb;
    }
    private string CheckVacancyID()
    {
        string sUser = "";
        try
        {
            DataTable tb = new DataTable();
            tb = SelectVacancyID();
            if (tb.Rows.Count > 0)
            {
                string ID = tb.Rows[tb.Rows.Count - 1]["VacancyID"].ToString();
                char[] cID = ID.ToArray();
                for (int i = (ID.Count() - 1); i >= 0; i--)
                {
                    try
                    {
                        int iu = Convert.ToInt32(cID[i].ToString());
                        if (iu < 9)
                        {
                            iu++;
                            sUser += iu;
                            for (int j = i-1; j >= 0; j--)
                            {
                                sUser += cID[j].ToString();
                            }
                            break;
                        }
                        else
                        {
                            iu = 0;
                            sUser += iu;
                        }
                    }
                    catch (Exception)
                    {
                        sUser += cID[i].ToString();
                    }
                }
                char[] cUser = sUser.ToArray();
                sUser = "";
                for (int i = (cUser.Count() - 1); i >= 0; i--)
                {
                    sUser += cUser[i].ToString();
                }
            }
            else
            {
                sUser = "V0001";
            }
        }
        catch (Exception)
        {
        }
        return sUser;
    }
    public bool InsertVacancy(string _Title, string _User, int _Jobtype, string _Descript, int _NumberOfJob, DateTime _DateCreate, DateTime _DateFulfilled)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE CreateNewVacancy @ID, @Title, @User, @typeJob, @Descript, @Number, @DateCreate, @DateFulfilled, 1", conn);
            comm.Parameters.AddWithValue("@ID", CheckVacancyID());
            comm.Parameters.AddWithValue("@Title", _Title);
            comm.Parameters.AddWithValue("@User", _User);
            comm.Parameters.AddWithValue("@typeJob", _Jobtype);
            comm.Parameters.AddWithValue("@Descript", _Descript);
            comm.Parameters.AddWithValue("@Number", _NumberOfJob);
            comm.Parameters.AddWithValue("@DateCreate", _DateCreate);
            comm.Parameters.AddWithValue("@DateFulfilled", _DateFulfilled);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int iresult = comm.ExecuteNonQuery();
            if (iresult > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return result;
    }
    public DataTable TypeOfWork()
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("Select * from SelectTypeJob", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception)
        {
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return tb;
    }
    //Methord in page CreateInterview

    //Select Vacancy of Employee
    public DataTable SelectVacancy(string _EmployeeID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE InterviewVacancy @ID", conn);
            comm.Parameters.AddWithValue("@ID", _EmployeeID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "Vacancy");
            tb = ds.Tables["Vacancy"];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return tb;
    }
    //Methord return Applicants follow vacancy
    public DataTable SelectApplicant(string _VacancyID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE InterviewApplicant @ID", conn);
            comm.Parameters.AddWithValue("@ID", _VacancyID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "Applicant");
            tb = ds.Tables["Applicant"];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return tb;
    }
    public string ReturnVacancyID(string _Title)
    {
        string VacancyID = "";
        try
        {
            SqlCommand comm = new SqlCommand("SELECT * FROM SelectVacancyID", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "Vacancy");
            DataTable tb = ds.Tables["Vacancy"];
            if (tb.Rows.Count > 0)
            {
                foreach (DataRow item in tb.Rows)
                {
                    if (item["Title"].ToString().Equals(_Title))
                    {
                        VacancyID = item["VacancyID"].ToString();
                        break;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return VacancyID;
    }
    //Methord return list Interviewer for vacancy
    public DataTable SelectInterviewer(string _VacancyID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE InterviewIntervewer @ID", conn);
            comm.Parameters.AddWithValue("@ID", ReturnJobID(_VacancyID));
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "Intervewer");
            tb = ds.Tables["Intervewer"];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return tb;
    }
    //Methord return JobID in table Vacancy
    private string ReturnJobID(string _VacancyID)
    {
        string ID = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE VacancyReturnJobID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _VacancyID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "JobID");
            DataTable tb = ds.Tables["JobID"];
            if (tb.Rows.Count > 0)
            {
                foreach (DataRow item in tb.Rows)
                {
                    ID = item["JobID"].ToString();
                }
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if(conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return ID;
    }
    //Select Fullname and ID of HR
    public string SelectNameEmployee(string _UserName)
    {
        string FulllName = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE FullnameEmployee @ID", conn);
            comm.Parameters.AddWithValue("@ID", _UserName);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable tb = ds.Tables[0];
            if (tb.Rows.Count > 0)
            {
                foreach (DataRow item in tb.Rows)
                {
                    FulllName = item["FullName"].ToString();
                }
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return FulllName;
    }
    //Methord insert into table interviewdetail in database
    public bool InsertintoInterviewdetail(string _ApplicantID, string _InterviewerID, string _Date, string _Time, int _Status)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE InsertInterviewDetails", conn);
            comm.Parameters.Add("@ApplicantID", SqlDbType.VarChar).Value = _ApplicantID;
            comm.Parameters.Add("@InterviewerID", SqlDbType.VarChar).Value = _InterviewerID;
            comm.Parameters.Add("@Date", SqlDbType.VarChar).Value = _Date;
            comm.Parameters.Add("@Time", SqlDbType.VarChar).Value = _Time;
            comm.Parameters.Add("@Status", SqlDbType.VarChar).Value = _Status;
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int insert = comm.ExecuteNonQuery();
            if (insert > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return result;
    }
}